﻿using System;
using System.Data.SqlServerCe;
using System.Collections.Generic;
using SerbinAV.MobileSales.Sales.Entities;
using System.Data;

namespace SerbinAV.MobileSales.Sales.DAL
{
    public class VisitRepository:IVisitRepository
    {
        private string _connectionString;

        public VisitRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        private List<Visit> LoadVisits(SqlCeCommand command)
        {
            List<Visit> visits = new List<Visit>();
            using (SqlCeDataReader dr = command.ExecuteReader())
            {
                while (dr.Read())
                {
                    Visit visit = EntityReader.ReadVisit(dr);
                    visit.BuyPoint = EntityReader.ReadBuyPoint(dr);
                    visit.LoadOrder += new OrderRepository(_connectionString).GetOrdersByVisit;
                    visits.Add(visit);
                }
                return visits;
            }
        }

        public List<Visit> GetVisitsByDate(DateTime dateWork)
        {
            DateTime endDate = (dateWork.AddHours(23).AddMinutes(59).AddSeconds(59)).Date;
            return GetVisitsByDate(dateWork, endDate);
        }

        public List<Visit> GetVisitsByDate(DateTime beginDateWork, DateTime endDateWork)
        {
            using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery =
                    "SELECT Visits.IdVisit, Visits.DateVisit, Visits.BuyPointId, Visits.TimeEndVisit, Visits.IsExport, Visits.GpsData, "+
                            "BuyPoints.IdBuyPoint, BuyPoints.NameBuyPoint, BuyPoints.Adress, BuyPoints.BuyerId, BuyPoints.Debts, "+
                            "COUNT(Orders.IdOrder) AS IsOrder "+
                    "FROM BuyPoints "+
                        "INNER JOIN Visits ON BuyPoints.IdBuyPoint = Visits.BuyPointId "+
                        "LEFT OUTER JOIN Orders ON Visits.IdVisit = Orders.VisitId "+
                    "GROUP BY Visits.IdVisit, Visits.DateVisit, Visits.BuyPointId, Visits.TimeEndVisit, Visits.IsExport, Visits.GpsData, "+
                            "BuyPoints.IdBuyPoint, BuyPoints.NameBuyPoint, BuyPoints.Adress, BuyPoints.BuyerId, BuyPoints.Debts " +
                    "Having visits.DateVisit between @startDateVisits and @endDateVisits ";
                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    command.Parameters.Add("@startDateVisits", beginDateWork.Date);
                    command.Parameters.Add("@endDateVisits", endDateWork.Date);
                    return LoadVisits(command);
                }
            }
        }

        //public List<Visit> GetAllVisitsForExport()
        //{
        //    using (SqlCeConnection connection = WorkDataBase.CreateConnection())
        //    {
        //        if (connection.State != ConnectionState.Open)
        //            connection.Open();
        //        string sqlQuery =
        //            "SELECT IdVisit, DateVisit, BuyPointId, TimeEndVisit, GpsData, IsExport, GpsData FROM Visits" +
        //            " where IsExport = 0 and TimeEndVisit is Not Null";
        //        using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
        //        {
        //            return LoadVisits(command, true);
        //        }
        //    }

        //}
    
        public void CreateVisit(Visit visit)
        {

            using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery = "insert INTO Visits (DateVisit, BuyPointId) values (@DateVisit, @BuyPointId)";
                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    command.Parameters.Add("@DateVisit", SqlDbType.DateTime).Value = visit.DateVisit;
                    command.Parameters.Add("@BuyPointId", SqlDbType.Int).Value = visit.BuyPoint.IdBuyPoint;
                    if (command.ExecuteNonQuery() < 1)
                    {
                        throw new Exception("Не добавлено жодного запису");
                    }
                }
            }

        }

        public void UpdateVisit(Visit visit)
        {
            using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery = "Update Visits set " +
                                  "TimeEndVisit = @TimeEndVisit, IsExport = @IsExport, GpsData = @GpsData where idVisit = @IdVisit";
                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    command.Parameters.Add("@GpsData", visit.GpsData);
                    command.Parameters.Add("@TimeEndVisit", SqlDbType.DateTime).Value = visit.TimeEndVisit;
                    command.Parameters.Add("@IsExport", SqlDbType.Bit).Value = visit.IsExport;
                    command.Parameters.Add("@IdVisit", SqlDbType.Int).Value = visit.IdVisit;
                    if (command.ExecuteNonQuery() < 1)
                    {
                        throw new Exception("Не змінено жодного запису");
                    }
                }
            }

        }

        public void DeleteVisit(Visit visit)
        {
            if (visit.IsExport)
            {
                throw new Exception("Неможна видаляти експортований візит");
            }
            if (visit.IsOrder != null && visit.IsOrder.Value)
            {
                throw new Exception("Неможна видаляти візит з продажою");
            }

            if (visit.IsVisit)
            {
                throw new Exception("Неможна видаляти відмічений візит");
            }

            DeleteVisit(visit.IdVisit);
        }

        private void DeleteVisit (int id)
        {
            using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                string sqlQuery = "Delete from Visits where IdVisit = @IdVisit";
                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    command.Parameters.Add("@IdVisit", id);
                    if (command.ExecuteNonQuery() < 1)
                    {
                        throw new Exception("Не видалено жодного запису");
                    }
                }
            }
        }

        public void CreateVisitFromGraphics(DateTime dateTime)
        {
            using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                string sqlQuery =
                        "insert INTO Visits (DateVisit, BuyPointId) SELECT @DateVisit " +
                        "AS DateVisit, BuyPointId FROM GraphicsVisits WHERE " + dateTime.DayOfWeek.ToString() +
                        " = 1";
                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    command.Parameters.Add("@DateVisit", dateTime.Date);
                    if (command.ExecuteNonQuery() < 1)
                    {
                        throw new Exception("Не добавлено жодного запису");
                    }
                }
            }
        }

    }
}

